#!/usr/bin/env python
# encoding: utf-8
'''
@author: 迟乐飞 
@contact: lefei_chi@foxmail.com
@software: pycharm
@file: airbnb_data.py
@time: 2019-11-08 15:41
@desc:airbnb_agv_price 插入到 wh_house_cal
'''
import os, sys

pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)
from report_system.utils.db_util import con_mysql_cityhome, con_mysql_report, con_mysql_report_test, con_mysql_tpp, con_mysql_youjia
import logging


class AirbnbData(object):

    def update_wh_house_cal(self):
        sql_airbnb_avg_price = "SELECT house_id,avg_night_price_market,start_date FROM dws_business_data WHERE start_date =date_format(CURRENT_DATE - 1, '%Y-%m-%d')"

        sql_logde_id = "SELECT listing_id, lodge_id FROM airbnb_upload_lodge "

        sql_logde_house_id = "SELECT lodge_id,id FROM lod_house WHERE lodge_id is not null"

        # 城宿后台数据库中的airbnb数据
        try:
            airbnb_avg_price = con_mysql_cityhome.find_all_as_dict_low(sql_airbnb_avg_price)
        except Exception as e:
            logging.error(e)

        # youjia表中房源与airbnb中的listing_id的对应
        try:
            logde_id_dict = {}
            logde_id = con_mysql_youjia.find_all_as_dict_low(sql_logde_id)
            for content in logde_id:
                logde_id_dict.update({content['listing_id']: content['lodge_id']})
        except Exception as e:
            logging.error(e)
        # 房屋与房源对应
        try:
            logde_house_id_dict = {}
            logde_house_id = con_mysql_youjia.find_all_as_dict_low(sql_logde_house_id)
            for content in logde_house_id:
                logde_house_id_dict.update({content['lodge_id']: content['id']})
        except Exception as e:
            logging.error(e)

        for content in airbnb_avg_price:
            try:
                house_id = logde_house_id_dict[logde_id_dict[int(content['house_id'])]]
                # 关联到 airbnb中的id与房屋对应
                update_sql = 'update wh_house_calendar set airbnb_avg_price = {avg_price} where house_id ={house_id} and dt = current_date -1 and order_channel = 20'.format(
                    avg_price=round(content['avg_night_price_market'], 2), house_id=house_id)
                con_mysql_report.insert(update_sql)
            except Exception as e:
                print(e)
                continue
            print(update_sql)

if __name__ == '__main__':
    AirbnbData().update_wh_house_cal()
